/**
 * 用来同步数据与sql文件的工具
 * @author yangxiao
 * @module tools/sqlsync
 */

import * as mysql from 'mysql';
import * as fs from 'fs';
import * as path from 'path';
import * as async from 'async';
import * as config from '../lib/config/config';
import * as stringFormat from '../util/string-format'

let newtables = [];
let newsqls = [];
let execQuerySqls = [];

//需要有设置库的权限，请不要设置database;
let dbConfig = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: ''
});
let fileList = {};
let connectionPool = {};
let db: any = {};

let newDatabasePromis = [];
let newTablePromis = [];
config.getServerConfig().mysql.forEach((value, index, array) => {

    var filePath = 'asset/server/sql/' + value.dbName + ".sql";
    var file = config.getOtherFile(filePath);
    if (file == null) {
        console.error("error file not  ", filePath);
    } else {
        fileList[value.dbName] = file;
        connectionPool[value.dbName] = mysql.createPool({
            host: value.host,
            user: value.user,
            password: value.pwd,
            database: value.dbName,
        });
        newDatabasePromis.push((done) => {
            getCreateDBPromise(value.dbName, dbConfig, done);
        });
        newTablePromis.push((done) => {
            getCreateTablePromise(value.dbName, file, connectionPool[value.dbName], done);
        });
        createSqlBaseClass(value.dbName);
    }

});

async.series(newDatabasePromis.concat(newTablePromis), function (error, result) {
    console.log("sql sync ok");
})


function createSqlBaseClass(dbName, _path: string = '/src/app/sql') {

    if(!config.isDebug){
        return;
    }
    if (dbName == null) {
        return;
    }
    let filePath = config.getRootPath() + path.normalize(_path+"/") + dbName;
    let baseClassPath = filePath +path.normalize("/base.ts") ;
    if (!fs.existsSync(filePath)) {
        console.log("create",filePath);
        fs.mkdirSync(filePath);
    }
    if (!fs.existsSync(baseClassPath)) {
         console.log("create",baseClassPath);
        var tempCodeTemplate = `
           import * as mysql from '../../../lib/mysql/mysql';
            /**
             * 数据库的执行的基类，当前目录中新建的sql文件都要继承于它
             */

            /**
             * 必须设置库名，一般和文件夹同名，本文件夹下的所有类，都要继承这个类。
             */
            export let dbName = '%s';

            /**
             * 执行相关的查询
             */
            export function query(sql, values, callback?: (err, result?, fields?) => any){
                mysql.query(sql,values,(err,result)=>{
                    callback(err,result);
                },dbName);
            }
           `;
        fs.writeFileSync(baseClassPath, stringFormat.format(tempCodeTemplate,dbName));
    }
}

function getCreateDBPromise(name: string, connection, done) {

    connection.getConnection((err, client) => {
        if (err != null) {
            done(err);
        }
        let sql = 'CREATE DATABASE  IF NOT EXISTS `' + name + '` DEFAULT CHARACTER SET utf8';
        client.query(sql, [], (err, res) => {
            if (err != null) {
                done(err);
            }
            if (res.warningCount == 0) {
                console.log('create databse ', name);
            }
            client.release();
            done(null, res);
        });
    });
}



function getCreateTablePromise(dbName, data: string, connection, done) {

    // 清空全局数据
    newtables = [];
    newsqls = [];
    execQuerySqls = [];
    db = connection;
    executeSQL(dbName, data, done);

}



function executeSQL(dbName, sql, callback) {
    /*****************处理字符串，去掉一些注释的代码**********************/

    //去除如/***/的注释
    sql = sql.replace(/(\/\*(\n|.)*?\*\/;)/g, '');
    //console.log(sql);

    //去除如--类的注释
    sql = sql.replace(/(--.*?\n)/g, '');
    //console.log(sql);

    var tablesMatches = sql.match(/CREATE TABLE (IF NOT EXISTS.+?)?.* /g);
    //console.log(tablesMatches)
    tablesMatches.forEach(function (tmp) {
        var a = tmp.trim().split(' ');
        newtables.push(a[a.length - 1].replace('`', '').replace('`', ''));
        var startIndex = sql.indexOf(tmp);
        var endIndex = sql.indexOf(';', startIndex) + 1;
        newsqls.push(sql.substring(startIndex, endIndex));
    })

    var tableLen = newtables.length;
    //for (var num = 0; num < tableLen; num++) {
    //    dealTable(num);
    //}
    //Log.info("处理表个数："+tableLen);
    dealTable(dbName, 0, tableLen, callback);
}

function dealTable(dbName, num, tableLen, callback) {
    var newcols = getcolumn(newsqls[num]);
    var newtable = newtables[num];
    var oldtable = newtable;
    var checksql = "SHOW CREATE TABLE " + newtable;
    db.query(checksql, [], function (err, query) {
        var usql;
        if (!query || query.length == 0) {
            usql = newsqls[num];
            usql = usql.replace(oldtable, newtable);
            execQuerySqls.push(usql);
        } else {
            var value = query[0];

            // 判断注释
            var comment = checkTableComment(newsqls[num], value['Create Table'])
            if (comment) {
                usql = "ALTER TABLE " + newtable + " COMMENT =  '" + comment + "'";
                execQuerySqls.push(usql);
            }

            var oldcols = getcolumn(value['Create Table']);
            var updates = [];
            var allfileds = Object.keys(newcols);
            for (var i = 0, len = allfileds.length; i < len; i++) {
                var filedKey = allfileds[i];
                var filedValue = newcols[filedKey];
                if (filedKey == 'PRIMARY') {
                    if (filedValue != oldcols[filedKey]) {
                        if (oldcols[filedKey]) {
                            usql = "RENAME TABLE " + newtable + " TO " + newtable + '_bak';
                            execQuerySqls.push(usql);
                        }
                        updates.push("ADD PRIMARY KEY " + filedValue);
                    }
                } else if (filedKey == 'KEY') {

                    for (var subkey in filedValue) {
                        var subvalue = filedValue[subkey];
                        if (oldcols['UNIQUE'] && oldcols['UNIQUE'][subkey]) {
                            updates.push("DROP INDEX `" + subkey + "`");
                        }
                        if (oldcols['KEY'] && oldcols['KEY'][subkey]) {
                            if (subvalue != oldcols['KEY'][subkey]) {
                                updates.push("DROP INDEX `" + subkey + "`");
                                updates.push("ADD INDEX `" + subkey + "` " + subvalue + "");
                            }
                        } else {
                            updates.push("ADD INDEX `" + subkey + "` " + subvalue + "");
                        }
                    }
                } else if (filedKey == 'UNIQUE') {
                    for (var subkey in filedValue) {
                        var subvalue = filedValue[subkey];
                        if (oldcols['KEY'] && oldcols['KEY'][subkey]) {
                            updates.push("DROP INDEX `" + subkey + "`");
                        }
                        if (oldcols['UNIQUE'] && oldcols['UNIQUE'][subkey]) {
                            if (subvalue != oldcols['UNIQUE'][subkey]) {
                                updates.push("DROP INDEX `" + subkey + "`");
                                updates.push("ADD UNIQUE INDEX `" + subkey + "` " + subvalue + "");
                            }
                        } else {
                            updates.push("ADD UNIQUE INDEX `" + subkey + "` " + subvalue + "");
                        }
                    }
                } else {
                    if (oldcols[filedKey]) {
                        filedValue = filedValue.replace(/DEFAULT\s+([0-9]+)/i, "DEFAULT '\$1'");
                        if (filedValue.toLowerCase() != oldcols[filedKey].toLowerCase()) {
                            updates.push("CHANGE `" + filedKey + "` `" + filedKey + "` " + filedValue + "");
                        }
                    } else {
                        var i = allfileds.indexOf(filedKey);
                        var fieldposition = i >= 0 ? 'AFTER `' + allfileds[i - 1] + '`' : 'FIRST';
                        updates.push("ADD `" + filedKey + "` " + filedValue + " " + fieldposition + "");
                    }
                }
            }


            // update
            if (updates && updates.length) {
                usql = "ALTER TABLE " + newtable + " " + updates.join(', ');
                execQuerySqls.push(usql);
            }

            // del
            var deletes = [];
            for (var colname in oldcols) {
                var definition = oldcols[colname];
                var a = ['UNIQUE', 'KEY', 'PRIMARY'];
                if (a.indexOf(colname) != -1) {
                    continue;
                }

                if (!newcols[colname]) {
                    deletes.push("DROP `" + colname + "`");
                }
            }

            if (deletes && deletes.length) {
                usql = "ALTER TABLE " + newtable + " " + deletes.join(', ');
                execQuerySqls.push(usql);
            }
        }

        //dealTableLen++;
        num++;
        if (num >= tableLen) {
            execQuerys(dbName, callback);
        } else {
            dealTable(dbName, num, tableLen, callback);
        }
    });
}

function execQuerys(dbName, callback) {
    if (execQuerySqls.length == 0) {
        console.log(dbName, '不需要更新');
        callback(0);
        return;
    } else {
        var execIndex = 0;
        execQuerySqls.forEach(function (sql) {
            execQuery(sql, function (err) {
                // 记录执行SQL
                //console.info(execQuerySqls[execIndex]);
                execIndex++;
                if (err) {
                    callback(err);
                    return false;
                }
                if (execIndex >= execQuerySqls.length) {
                    callback(0);
                }
            });
        });
    }
}

function execQuery(sql, callback) {
    db.query(sql, [], function (err, $res) {
        if (err) {
            //console.error('[ERROR] ['+sql+'] ' + err + '\n');
            //Log.info('[ERROR] ['+sql+'] ' + err);
            //callback(err);
        } else {
            console.info('[SUCCESS] [' + sql + '] ');
        }
        callback(0);
    });
}

function checkTableComment(newSql, oldSql): any {
    if (!newSql || !oldSql) {
        return false;
    }

    // 获取最后一行
    var newlastSql = newSql.split('\n').pop();
    var oldlastSql = oldSql.split('\n').pop();
    var newComment = '';
    var oldComment = '';
    var matchs = newlastSql.match(/COMMENT\='(.*)'/g);
    if (matchs)
        newComment = matchs[0];

    if (!newComment)
        return false;

    matchs = oldlastSql.match(/COMMENT\='(.*)'/g);
    if (matchs)
        oldComment = matchs[0];

    if (newComment == oldComment)
        return false;

    return newComment;
}

function remakesql(value) {
    value = value.trim();
    value = value.replace('`', '').replace('`', '').replace(', ', ',').replace(' ,', ',').replace('( ', '(').replace(' )', ')').replace('mediumtext', 'text');
    return value;
}

function getcolumn(creatsql) {
    var cols = creatsql.split('\n');
    cols.pop();
    cols.shift();
    var newcols = {};
    cols.forEach(function (value) {
        value = value.trim();
        if (value.length == 0)
            return;
        value = remakesql(value);
        if (value.substr(value.length - 1, 1) == ',')
            value = value.substr(0, value.length - 1);

        var vs = value.split(' ');
        var cname = vs[0];

        if (cname == 'KEY' || cname == 'INDEX' || cname == 'UNIQUE') {
            var name_length = cname.length;
            if (cname == 'UNIQUE')
                name_length = name_length + 4;

            var subvalue = value.substr(name_length).trim();
            var subvs = subvalue.split(' ');
            var subcname = subvs[0];
            if (!newcols[cname])
                newcols[cname] = {};
            newcols[cname][subcname] = value.substr(name_length + 2 + subcname.length).trim();
        } else if (cname == 'PRIMARY') {
            newcols[cname] = value.substr(11).trim();
        } else {
            newcols[cname] = value.substr(cname.length).trim();
        }
    });
    return newcols;
}

